using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: daiduong19051986@gmail.com
    /// <summary>
    public class RuleProfileDataAccess : Connection
    {
        #region Local Variable
        private string _strTableName = "[RuleProfileRV]";
        private string _strSPGetTransferOutName = "dbo.[procRuleProfileRV_gettransferout]";

        #endregion Local Variable

        #region Method
        public RuleProfileInfo Get(string db, String RULESET_CODE, ref string sErr)
        {
            string sql = @"SELECT        RULESET_CODE, DESCRIPTN, TVC_TB, SUSPEND
            FROM            RuleProfile" + db + @"
            WHERE        (RULESET_CODE = @RULESET_CODE)";
            RuleProfileInfo objEntr = new RuleProfileInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(RuleProfileInfo.Field.RULESET_CODE.ToString(), RULESET_CODE);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (list.Rows.Count > 0)
                objEntr = new RuleProfileInfo(list.Rows[0]);
            else return new RuleProfileInfo();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }



        public DataTable GetAll(string db, ref string sErr)
        {
            string sql = @"SELECT        RULESET_CODE, DESCRIPTN, TVC_TB, SUSPEND, (RULESET_CODE + ' - ' + DESCRIPTN) as DisplayCBO
            FROM            RuleProfile" + db;
            InitConnect();
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(string db, RuleProfileInfo objEntr, ref string sErr)
        {
            string sql = @"INSERT INTO RuleProfile" + db + @"
                         (RULESET_CODE, DESCRIPTN, TVC_TB, SUSPEND)
            VALUES        (@RULESET_CODE,@DESCRIPTN,@TVC_TB,@SUSPEND)";
            int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(RuleProfileInfo.Field.RULESET_CODE.ToString(), objEntr.RULESET_CODE);
            AddParameter(RuleProfileInfo.Field.DESCRIPTN.ToString(), objEntr.DESCRIPTN);
            AddParameter(RuleProfileInfo.Field.TVC_TB.ToString(), objEntr.TVC_TB);
            AddParameter(RuleProfileInfo.Field.SUSPEND.ToString(), objEntr.SUSPEND);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(string db, RuleProfileInfo objEntr)
        {
            string sql = @"UPDATE       RuleProfile" + db + @"
            SET                DESCRIPTN = @DESCRIPTN, TVC_TB = @TVC_TB, SUSPEND = @SUSPEND
            WHERE        (RULESET_CODE = @RULESET_CODE)";
            openConnection();
            InitCommand(sql);
            AddParameter(RuleProfileInfo.Field.RULESET_CODE.ToString(), objEntr.RULESET_CODE);
            AddParameter(RuleProfileInfo.Field.DESCRIPTN.ToString(), objEntr.DESCRIPTN);
            AddParameter(RuleProfileInfo.Field.TVC_TB.ToString(), objEntr.TVC_TB);
            AddParameter(RuleProfileInfo.Field.SUSPEND.ToString(), objEntr.SUSPEND);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db, String RULESET_CODE)
        {
            string sql = @"DELETE FROM RuleProfile" + db + @"
            WHERE        (RULESET_CODE = @RULESET_CODE)";
            openConnection();
            InitCommand(sql);
            AddParameter(RuleProfileInfo.Field.RULESET_CODE.ToString(), RULESET_CODE);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }



        public Boolean IsExist(string db, String RULESET_CODE)
        {
            string sql = @"SELECT TOP (1) RULESET_CODE 
            FROM RuleProfile" + db + @"
            WHERE        (RULESET_CODE = @RULESET_CODE)";
            InitConnect();
            InitCommand(sql);
            AddParameter(RuleProfileInfo.Field.RULESET_CODE.ToString(), RULESET_CODE);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(RuleProfileInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public DataTable GetAllby(string db, string aTable, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     RULESET_CODE
                FROM         RuleProfile" + db + @"
                WHERE   TVC_TB = @TVC_TB AND (SUSPEND = 0)", CommandType.Text);

            AddParameter("TVC_TB", aTable);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetDetail(string db, string aTable, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT     d.RULESET_CODE, d.COMMAND, d.FIELD, d.START, d.LENGTH, d.OPERATOR, d.VALUE, d.COL0, d.COL1, d.COL2, d.COL3, d.COL4, d.COL5, d.COL6, d.COL7, d.COL8, d.COL9
                FROM         RuleSet" + db + @" AS d INNER JOIN
                      RuleProfile" + db + @" AS m ON d.RULESET_CODE = m.RULESET_CODE
                WHERE     (m.TVC_TB = @TVC_TB) AND m.SUSPEND = 0
                ORDER BY d.AUTO_ID";

            InitCommand(sql, CommandType.Text);

            AddParameter("TVC_TB", aTable);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }



            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetResult(string db, string aTable, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT     d.RESULT_ID, d.RULESET_CODE, d.MESSAGE, d.IS_ERROR, d.WHERE_CLAUSE
                FROM         RuleResult" + db + @" AS d INNER JOIN
                                      RuleProfile" + db + @" AS m ON d.RULESET_CODE = m.RULESET_CODE
                WHERE     (m.TVC_TB = @TVC_TB) AND (m.SUSPEND = 0)
                ORDER BY d.RESULT_ID";
            InitCommand(sql, CommandType.Text);

            AddParameter("TVC_TB", aTable);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }



            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public void addResult(string db, DataRow r, ref string sErr)
        {
            openConnection();
            string sql = @"INSERT INTO RuleResult" + db + @"
                      (RESULT_ID, RULESET_CODE, MESSAGE, IS_ERROR, WHERE_CLAUSE)
                VALUES     (@RESULT_ID,@RULESET_CODE,@MESSAGE,@IS_ERROR,@WHERE_CLAUSE)";

            InitCommand(sql, CommandType.Text);

            AddParameter("RESULT_ID", r["RESULT_ID"]);
            AddParameter("RULESET_CODE", r["RULESET_CODE"]);
            AddParameter("MESSAGE", r["MESSAGE"]);
            AddParameter("IS_ERROR", r["IS_ERROR"]);
            AddParameter("WHERE_CLAUSE", r["WHERE_CLAUSE"]);

            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            if (sErr != "") ErrorLog.SetLog(sErr);
        }

        public string delResult(string db, string RULESET_CODE)
        {
            openConnection();
            InitCommand(@"DELETE FROM RuleResult" + db + @"
                WHERE     (RULESET_CODE = @RULESET_CODE)", CommandType.Text);
            AddParameter(RuleProfileInfo.Field.RULESET_CODE.ToString(), RULESET_CODE);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
    }
}
